package com.xdjmall.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import com.xdjmall.dao.CartItemDao;
import com.xdjmall.entity.CartItem;
import com.xdjmall.util.DBUtils;
import com.xdjmall.util.QueryCondition;
import com.xdjmall.util.SQLHandler;

public class CartItemDaoImpl implements CartItemDao {
	@Override
	public Integer insert(CartItem t) throws SQLException {
		// 文娟
		String sql = "insert into cart_item(user_id,goods_id,goods_count) values(?,?,?);";
		try (Connection c = DBUtils.getConnection();
				PreparedStatement ps = c.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);) {
			ps.setInt(1, t.getUserId());
			ps.setInt(2, t.getGoodsId());
			ps.setInt(3, t.getGoodsCount());
			int row = ps.executeUpdate();
			ResultSet keySet = ps.getGeneratedKeys();
			if (keySet.next()) {
				t.setCartItemId(keySet.getInt(1));
			}
			return row;
		}
	}

	@Override
	public Integer delete(Integer id) throws SQLException {
		// 文娟
		String sql = "delete from cart_item where cart_item_id = ?";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			ps.setInt(1, id);
			int row = ps.executeUpdate();
			return row;
		}
	}

	@Override
	public Integer update(CartItem t) throws SQLException {
		// 文娟
		String sql = "update cart_item set ";
		if (t.getUserId() != null) {
			sql += " user_id=?,";
		}
		if (t.getGoodsId() != null) {
			sql += " goods_id=?,";
		}
		if (t.getGoodsCount() != null) {
			sql += " goods_count=?,";
		}
		// 截取并删除逗号 substring 包前不包后
		sql += " update_time=now() where cart_item_id=?";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			int num = 1;
			if (t.getUserId() != null) {
				ps.setInt(num++, t.getUserId());
			}
			if (t.getGoodsId() != null) {
				ps.setInt(num++, t.getGoodsId());
			}
			if (t.getGoodsCount() != null) {
				ps.setInt(num++, t.getGoodsCount());
			}
			ps.setInt(num, t.getCartItemId());
			return ps.executeUpdate();

		}
	}

	@Override
	public CartItem selectById(Integer id) throws SQLException {
		// 文娟
		String sql = "SELECT cart_item_id, user_id, goods_id, goods_count, is_deleted FROM cart_item WHERE cart_item_id = ?";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				CartItem cti = new CartItem();
				cti.setCartItemId(rs.getInt(1));
				cti.setUserId(rs.getInt(2));
				cti.setGoodsId(rs.getInt(3));
				cti.setGoodsCount(rs.getInt(4));
				cti.setIsDeleted(rs.getBoolean(5));
				return cti;
			}
		}
		return null;
	}

	@Override
	public Integer selectCount() throws SQLException {
		// 文娟
		String sql = "select count(*) from cart_item";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			ResultSet rs = ps.executeQuery();
			rs.next();
			int row = rs.getInt(1);
			c.close();
			return row;
		}
	}

	@Override
	public List<CartItem> selectByPage(QueryCondition condition) throws SQLException {
		// FIXME 纪源
		List<CartItem> list = new ArrayList<>();
		String sql = "select * from cart_item ";

		Integer page = condition.getPage();
		Integer pageSize = condition.getPageSize(); // 分页条件
		if (page != null && pageSize != null) {
			sql += " limit ?,? ";
		}
		int num = 1;
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {

			if (page != null && pageSize != null) {
				ps.setInt(num, (page - 1) * pageSize);
				num++;
				ps.setInt(num, pageSize);
			}
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				CartItem g = new CartItem();
				g.setGoodsId(rs.getInt(1));
				g.setCartItemId(rs.getInt(2));
				g.setUserId(rs.getInt(3));
				g.setGoodsCount(rs.getInt(4));
				g.setIsDeleted(rs.getBoolean(5));
				g.setCreateTime(rs.getDate(6).toLocalDate());
				g.setUpdateTime(rs.getDate(7).toLocalDate());
				list.add(g);
			}
		}
		return list.isEmpty() ? Collections.emptyList() : list;
	}

	@Override
	public CartItem selectByUserIdAndGoodsId(Integer userId, Integer goodsId) throws SQLException {
		// TODO 纪源
		String sql = "select user_id,goods_id,goods_count,is_deleted,create_time,update_time from cart_item "
				+ "where user_id =? and goods_id = ?; ";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {

			ps.setInt(1, userId);
			ps.setInt(2, goodsId);
			ResultSet rs = ps.executeQuery();
			CartItem m = new CartItem();
			if (rs.next()) {
				m.setUserId(rs.getInt(1));
				m.setGoodsId(rs.getInt(2));
				m.setGoodsCount(rs.getInt(3));
				m.setIsDeleted(rs.getBoolean(4));
				m.setCreateTime(rs.getDate(5).toLocalDate());
				m.setUpdateTime(rs.getDate(6).toLocalDate());
				return m;
			}
		}
		return null;
	}

	@Override
	public List<CartItem> selectByUserId(Integer userId, Integer number) throws SQLException {
		// FIXME 纪源
		String sql = "select cart_item_id,user_id,goods_id,goods_count,is_deleted,create_time,update_time from cart_item where user_id =? and is_deleted =0 limit ?";
		List<CartItem> cart = new ArrayList<>();
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			ps.setInt(1, userId);
			ps.setInt(2, number);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				CartItem its = new CartItem();
				its.setCartItemId(rs.getInt(1));
				its.setUserId(rs.getInt(2));
				its.setGoodsId(rs.getInt(3));
				its.setGoodsCount(rs.getInt(4));
				its.setIsDeleted(rs.getBoolean(5));
				its.setCreateTime(rs.getDate(6).toLocalDate());
				its.setUpdateTime(rs.getDate(7).toLocalDate());
				cart.add(its);
			}
			return cart.isEmpty() ? Collections.emptyList() : cart;
		}
	}

	@Override
	public Integer selectCountByUserId(Integer userId) throws SQLException {
		// TODO 张磊
		String sql = "select count(*) from cart_item where is_deleted=0 and user_id = ?";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			ps.setInt(1, userId);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			if (rs.next()) {
				count = rs.getInt(1);
			}
			return count;
		}
	}

	@Override
	public Integer deleteBatch(List<Integer> ids) throws SQLException {
		// TODO 张磊
		String sql = "update cart_item set update_time=now(), is_deleted = 1"
				+ " where is_deleted = 0 and cart_item_id " + SQLHandler.joinIn(ids);
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			return ps.executeUpdate();
		}
	}
}
